#自动汇总所有excel表的数据到一张总表上
import os
from openpyxl import load_workbook

#更新数据函数
def update_sta(filename):
    wb_fen=load_workbook(filename)
    wb_zong=load_workbook('总表.xlsx')
    sh_fen=wb_fen['shuju']
    sh_zong=wb_zong['shuju']
    maxrow_fen=sh_fen.max_row
    maxrow_zong=sh_zong.max_row
    maxcolumn_fen=sh_fen.max_column
    for m in range(2,maxrow_fen+1):
        for n in range(97,97+maxcolumn_fen)
            n=chr(n)
            i='%s%d'%(n,m)
            j='%s%d'%(n,m+maxrow_zong-1)
            cell_fen=sh_fen[i]
            sh_zong[j].value=cell_fen.value
    wb_zong.save('zong.xlsx')
    print('更新的文件为：',filename)
    wb_fen.close()
    wb_zong.close()

#读取文件夹中提交的所有数据表并更新
path=os.listdir(r'C:\Users\Desktop\excelautomation')#分表/汇总表都在一个文件夹下
for p in path:
    if p !='总表.xlsx' and p !='excel表汇总.py':
        update_sta(p)
print('更新完毕！')
